
package org.icnmasjid.member.dao.hibernate;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.icnmasjid.member.dao.LookupDao;
import org.icnmasjid.member.dao.PersonDao;
import org.icnmasjid.member.model.Person;
import org.icnmasjid.member.model.Role;
import org.icnmasjid.member.model.SubOrg;
import org.icnmasjid.member.util.CommonUtil;
import org.springframework.orm.ObjectRetrievalFailureException;

public class PersonDaoHibernate extends BaseDaoHibernate implements PersonDao {
	
	private LookupDao lookupDao;

    public int getPersonsCount(
    	       Person person
    ) {
        Criteria criteria = getPersonsCriteria(person);
        criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).setProjection(Projections.rowCount());
        List list = criteria.list();
        return (Integer)list.get(0);
    }


    public List<Person> getPersons(
            Person person
    ) {
    	return getPersons(person, null, null, null, null);
    }


    public List<Person> getPersons(
            Person person,
            String sortCriteria,
            String direction,
            Integer pageNumber,
            Integer numberOfResultsPerPage
    ) {
        Criteria criteria = getPersonsCriteria(person);
        if ( sortCriteria != null ) {
            if ( direction == null ) {
                direction = "asc";
            }
            if (direction.equals("asc")){
                criteria.addOrder(Order.asc(sortCriteria));
            } else {
                criteria.addOrder(Order.desc(sortCriteria));
            }
        }
        if ( pageNumber != null ) {
            if ( numberOfResultsPerPage == null ) {
                numberOfResultsPerPage = 10;
            }
              criteria.setFirstResult((pageNumber - 1) * numberOfResultsPerPage );
              criteria.setMaxResults(numberOfResultsPerPage);
        }
        return criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();
    }


    /**
     * @see org.icnmasjid.member.dao.PersonDao#getPersons(org.icnmasjid.member.model.Person)
     */
    public Criteria getPersonsCriteria(final Person person) {
		Criteria c = getSession().createCriteria(Person.class);
    	if ( person != null ) {
    		if ( person.getPersonId() != null ) {
    			c.add(Restrictions.eq("personId", person.getPersonId()));
    		}
    		if ( person.getRfid() != null ) {
    			c.add(Restrictions.eq("rfid", person.getRfid()));
    		}
    		if ( StringUtils.isNotBlank(person.getFirstName()) ) {
    			c.add(Restrictions.ilike("firstName", person.getFirstName().trim(), MatchMode.START));
    		}
    		if ( StringUtils.isNotBlank(person.getLastName()) ) {
    			c.add(Restrictions.ilike("lastName", person.getLastName().trim(), MatchMode.START));
    		}
    		if ( StringUtils.isNotBlank(person.getEmail()) ) {
    			c.add(
    					Restrictions.or(
    						Restrictions.ilike("email", person.getEmail().trim(), MatchMode.START),
    						Restrictions.ilike("email2", person.getEmail().trim(), MatchMode.START)
    					)
    			);
    		}
    		if ( StringUtils.isNotBlank(person.getPhoneHome()) ) {
    			c.add(
    					Restrictions.or(
    						Restrictions.or(
	    						Restrictions.like("phoneHome", person.getPhoneHome().trim(), MatchMode.START),
	    						Restrictions.like("phoneMobile", person.getPhoneHome().trim(), MatchMode.START)
	    					),
	    					Restrictions.or(
	    						Restrictions.like("phoneWork", person.getPhoneHome().trim(), MatchMode.START),
	    						Restrictions.like("phoneFax", person.getPhoneHome().trim(), MatchMode.START)
	    					)
    					)
    			);
    		}

    		boolean addressAliasAdded = false;
    		if ( StringUtils.isNotBlank(person.getSearchAddress().getAddress1()) ) {
    			if ( !addressAliasAdded ) {
    				c.createAlias("addresses", "address");
    				addressAliasAdded = true;
    			}
    			c.add(Restrictions.ilike("address.address1", person.getSearchAddress().getAddress1().trim(), MatchMode.START));
    		}
    		if ( StringUtils.isNotBlank(person.getSearchAddress().getCity()) ) {
    			if ( !addressAliasAdded ) {
    				c.createAlias("addresses", "address");
    				addressAliasAdded = true;
    			}
    			c.add(Restrictions.ilike("address.city", person.getSearchAddress().getCity().trim(), MatchMode.START));
    		}
    		if ( StringUtils.isNotBlank(person.getSearchAddress().getState()) ) {
    			if ( !addressAliasAdded ) {
    				c.createAlias("addresses", "address");
    				addressAliasAdded = true;
    			}
    			c.add(Restrictions.eq("address.state", person.getSearchAddress().getState().trim()));
    		}
    		if ( StringUtils.isNotBlank(person.getSearchAddress().getZip()) ) {
    			if ( !addressAliasAdded ) {
    				c.createAlias("addresses", "address");
    				addressAliasAdded = true;
    			}
    			c.add(Restrictions.ilike("address.zip", person.getSearchAddress().getZip().trim(), MatchMode.START));
    		}

/*
    		Example e1 = Example.create(person).enableLike(MatchMode.START).excludeZeroes().ignoreCase();
    		Example e2 = Example.create(person.getSearchAddress()).enableLike(MatchMode.START).excludeZeroes().ignoreCase();
    		return
    			getSession().createCriteria(Person.class)
    				.add(e1)
    				.createCriteria("addresses", Criteria.LEFT_JOIN)
    				.add(e2)
    				.list();
*/
    		if ( person.isSearchPrimary() ) {
    			c.add(Restrictions.eq("primaryPerson", Boolean.TRUE));
    		}
    		if ( person.isSearchVerified() ) {
    			c.add(Restrictions.isNull("verifiedDate"));
    		}

    		if ( person.getSearchMemberYear() != null ) {
				c.createAlias("memberInformations", "mi");
    			c.add(Restrictions.eq("mi.yearOfMembership", person.getSearchMemberYear()));
    		}

			if ( person.getSubOrg().getId() != null || person.getRole().getId() != null ) {
				c.createAlias("personSubOrgRoles", "psr");
				if ( person.getSubOrg().getId() != null ) {
					c.add(Restrictions.eq("psr.subOrg", person.getSubOrg()));
				}
				if ( person.getRole().getId() != null ) {
					c.add(Restrictions.eq("psr.role", person.getRole()));
				}
			}
			if ( person.getPersonStatus() != null ) {
				c.add(Restrictions.eq("personStatus", person.getPersonStatus()));
			}
			c.createAlias("parentPersonRelations", "ppr", Criteria.LEFT_JOIN);
    	}
		return c;
    }

    public List getPersonsbyExample(final Person person) {
    	if ( person != null ) {
    		return getHibernateTemplate().findByExample(person);
    	} else {
    		return getHibernateTemplate().find("from Person");
    	}
    }

    public Person getPersonByEmail(String email) {
    	if ( StringUtils.isNotBlank(email) ) {
        	List list = null;
        	StringBuffer qs = new StringBuffer("select distinct parent from Person parent")
    		.append(" where (parent.personStatus != ? or parent.personStatus is null)") // active
    		.append(" and ( parent.email = ? or parent.email2 = ? ) ")
    		.append(" ");
    		ArrayList<Object> values = new ArrayList<Object>();    	
    		values.add("Inactive");
    		values.add(email);
    		values.add(email);
    		list = getHibernateTemplate().find(qs.toString(), values.toArray());
    		if ( list.size() > 0 ) {
    			return (Person)list.get(0);
    		}
    	}
    	return null;
    }

    public List<Person> getPersonsWithEmail() {
		Criteria c = getSession().createCriteria(Person.class);
		c.add(
			Restrictions.or(
				Restrictions.ne("personStatus", "Inactive"),
				Restrictions.isNull("personStatus")
    		)
		);
		c.add(
			Restrictions.or(
				Restrictions.isNotNull("email"),
				Restrictions.isNotNull("email2")
    		)
		);
		c.createAlias("parentPersonRelations", "ppr", Criteria.LEFT_JOIN);
		c.createAlias("personRelations", "pr", Criteria.LEFT_JOIN);
		return c.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();
    }

    /**
     * @see org.icnmasjid.member.dao.PersonDao#getPerson(Integer personId)
     */
    public Person getPerson(final Integer personId) {
        Person person = (Person) getHibernateTemplate().get(Person.class, personId);
        if (person == null) {
            log.warn("uh oh, person with personId '" + personId + "' not found...");
            throw new ObjectRetrievalFailureException(Person.class, personId);
        }

        return person;
    }

    /**
     * @see org.icnmasjid.member.dao.PersonDao#savePerson(Person person)
     */
    public void savePerson(final Person person) {
        getHibernateTemplate().saveOrUpdate(person);
    }

    /**
     * @see org.icnmasjid.member.dao.PersonDao#removePerson(Integer personId)
     */
    public void removePerson(final Integer personId) {
        getHibernateTemplate().delete(getPerson(personId));
    }
/*    
    public List<Person> getParentsWithEmail(Role schoolYearRole) {
    	List list = null;
    	StringBuffer qs = new StringBuffer("select distinct parent from Person parent")
			.append(" left join fetch parent.personRelations pr2 ")    		
			.append(" left join fetch pr2.relatedPerson spouse")    		
    		.append(" left join fetch parent.personPayments pp")
    		.append(" left join fetch pp.paymentCategory pc")
    		.append(" left join fetch parent.personRelations pr")    		
    		.append(" left join fetch pr.relatedPerson student")
    		.append(" left join fetch student.personSubOrgRoles spsor")
    		.append(" left join fetch spsor.role studentRole")
    		.append(" left join fetch spsor.subOrg studentGrade")
    		.append(" where parent.personStatus = ?") // active
    		.append(" and pr.relationshipType = ?") // child
    		.append(" and studentRole = ?"); // 2010 student
    	ArrayList<Object> values = new ArrayList<Object>();    	
    	values.add("Active");
    	values.add(lookupDao.getRelationShipTypeByName("child"));
    	values.add(schoolYearRole);
    	list = getHibernateTemplate().find(qs.toString(), values.toArray());
    	return list;
    }
*/    
    public List<Person> getParentsWithEmail(Role schoolYearRole) {
    	List list = null;
    	StringBuffer qs = new StringBuffer("select distinct parent from Person parent")
		.append(" where (parent.personStatus != ? or parent.personStatus is null)") // active
		.append(" and exists ( select student from Person student ")
		.append(" inner join student.personSubOrgRoles spsor")
		.append(" inner join spsor.role studentRole")
		.append(", PersonRelation pr1 ")
		.append(" where pr1.relatedPerson = student and pr1.person = parent and pr1.relationshipType = ?")
		.append(" and studentRole = ? )")
		.append(" ");
		ArrayList<Object> values = new ArrayList<Object>();    	
		values.add("Inactive");
		values.add(lookupDao.getRelationShipTypeByName("child"));
		values.add(schoolYearRole);
		list = getHibernateTemplate().find(qs.toString(), values.toArray());
		if ( list.size() > 0 ) {
			return prefetch(list);
		}
		return list;
    }
    
    private List<Person> prefetch(List<Person> list) {
    	StringBuffer qs = new StringBuffer("select distinct parent from Person parent")
			.append(" left join fetch parent.personRelations pr2 ")    		
			.append(" left join fetch pr2.relatedPerson rp")
			.append(" left join fetch rp.personSubOrgRoles spsor")
			.append(" left join fetch spsor.role studentRole")
			.append(" left join fetch spsor.subOrg studentGrade")		
	    	.append(" left join fetch parent.personPayments pp")		
			.append(" left join fetch pp.paymentCategory pc");
    	qs.append(" where parent.id in ( ");
		ArrayList<Object> values = new ArrayList<Object>();    	
    	for ( Person p : list ) {
    		qs.append(" ? ,");
    		values.add(p.getPersonId());
    	}
    	qs.deleteCharAt(qs.length()-1);
    	qs.append(" )");
		List<Person> list1 = getHibernateTemplate().find(qs.toString(), values.toArray());
		return list1;
    }
    
    
    public List<Person> getParents(SubOrg grade, Role year) {
    	List list = null;
    	StringBuffer qs = new StringBuffer("select distinct parent from Person parent")
		.append(" left join fetch parent.personRelations pr2 ")    		
		.append(" left join fetch pr2.relatedPerson rp")    		
		.append(" where (parent.personStatus != ? or parent.personStatus is null)") // active
		.append(" and exists ( select student from Person student ")
		.append(" inner join student.personSubOrgRoles spsor")
		.append(" inner join spsor.role studentRole")
		.append(" inner join spsor.subOrg studentGrade")
		.append(", PersonRelation pr1 ")
		.append(" where pr1.relatedPerson = student and pr1.person = parent and pr1.relationshipType = ?")
		.append(" and studentRole = ? ")
		.append(" and studentGrade = ? )")
		.append(" ");
		ArrayList<Object> values = new ArrayList<Object>();    	
		values.add("Inactive");
		values.add(lookupDao.getRelationShipTypeByName("child"));
		values.add(year);
		values.add(grade);
		list = getHibernateTemplate().find(qs.toString(), values.toArray());
		return list;
    }


	public LookupDao getLookupDao() {
		return lookupDao;
	}


	public void setLookupDao(LookupDao lookupDao) {
		this.lookupDao = lookupDao;
	}
	
    public List<Person> getPersons(List<String> emails, List<String> numbers) {
    	List list = null;
    	StringBuffer qs = new StringBuffer("select distinct p from Person p")
		.append(" left join fetch p.personRelations pr ")    		
		.append(" left join fetch pr.relatedPerson rp")    		
		.append(" where (p.personStatus != :active or p.personStatus is null)") // active
		.append(" and ( p.email in (:emails)")
		.append(" or p.phoneHome in (:numbers) or p.phoneMobile in (:numbers) or p.phoneWork in (:numbers) or p.phoneFax in (:numbers) )");
		Query q = getSession().createQuery(qs.toString());
		q.setParameter("active", "Inactive");
		q.setParameterList("emails", emails, Hibernate.STRING);
		q.setParameterList("numbers", numbers, Hibernate.STRING);
		list = q.list();
		return list;
    }
    
    public Long getGradeCount(String grade1, String roleName) {
    	StringBuffer qs = new StringBuffer("select count(*) from PersonSubOrgRole psor")
		.append(" join psor.subOrg so ")    	
		.append(" join psor.role r ")		
		.append(" where so.name = :so_name")
		.append(" and r.name = :r_name");		
		Query q = getSession().createQuery(qs.toString());
		q.setParameter("so_name", grade1);
		q.setParameter("r_name", roleName);		
		return (Long)q.list().get(0);
    }
    
    public List<Person> getPrimaryPersonsWithEmail() {
    	List list = null;
    	StringBuffer qs = new StringBuffer(
    				"select distinct p from Person p ")
		.append(" 			left join fetch p.personRelations pr1 ")    		
		.append("			left join fetch pr1.relatedPerson rp1 ")    		    	
		.append(" 			where ( p.personStatus != ? or p.personStatus is null ) ") // active
		.append(" 			and not exists ( select pr from PersonRelation pr where pr.relatedPerson = p ) ")
		.append(" 			and ( p.email is not null or p.email2 is not null or ")
		.append(" 			exists ( select pr2 from PersonRelation pr2 where pr2.person = p ")
		.append("				and pr2.relationshipType.relationshipType = ? ")
		.append("				and (pr2.relatedPerson.email is not null or pr2.relatedPerson.email2 is not null)")
		.append("			))");
		ArrayList<Object> values = new ArrayList<Object>();    	
		values.add("Inactive");
		values.add("Spouse");
		list = getHibernateTemplate().find(qs.toString(), values.toArray());
		return list;
    }
    
    public Person getLoadedPerson(Integer personId) {
    	Person p = new Person();
    	p.setPersonId(personId);
    	return prefetch(Arrays.asList(new Person[]{p})).get(0);
    }
    
}
